/*****************************************************************************
 *   Copyright 2005 Tim A Wang
 *
 *   Licensed under the Apache License, Version 2.0 (the "License");
 *   you may not use this file except in compliance with the License.
 *   You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *   Unless required by applicable law or agreed to in writing, software
 *   distributed under the License is distributed on an "AS IS" BASIS,
 *   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *   See the License for the specific language governing permissions and
 *   limitations under the License.
 *
 ****************************************************************************/
package com.jb.web.model;

import com.jb.framework.DataSourceManager;
import com.jb.framework.database.Predicate;
import com.jb.framework.database.SelectDBCommand;
import com.jb.web.bean.inspector.PropertyInfo;
import com.jb.web.context.WebContext;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import com.jb.faces.context.FacesContext;

import com.jb.framework.util.BeanUtil;

import com.jb.util.ClassUtil;

import javax.servlet.jsp.el.ELException;

import javax.swing.event.ListDataListener;


/**
 * This implements database row set
 */
public class RowSet extends Variable implements JBListModel {
    static final long serialVersionUID = 123123L;

    static {
        publish(com.jb.web.model.RowSet.class, DATA, "selectStatement");
        publish(com.jb.web.model.RowSet.class, DATA, "countStatement");
        publish(com.jb.web.model.RowSet.class, DATA, "whereClause");
        publish(com.jb.web.model.RowSet.class, DATA, "groupBy");
        publish(com.jb.web.model.RowSet.class, DATA, "orderBy");
        publish(com.jb.web.model.RowSet.class, DATA, "fetchSize");
        publish(com.jb.web.model.RowSet.class, DATA, "beanClass");
    }

    List list;
    private Predicate predicate;
    private String beanClass;
    private String cachedStmt = "";
    private String countStatement = null;
    private String databaseUrl = "jdbc:mysql:///";
    private String groupBy;
    private String orderBy;
    private String selectStatement = null;
    private String whereClause;
    private boolean dirty = true;
    private int columns;

    //
    //fetch from a table is expensive, and should be avoided whenever necessary
    //if a predicate did not change, mostly like the data set would not have changed
    //so going to database would be a lose. This will only be invalid if the 
    //database has not been updated by other uses.
    //
    private int fetchSize = 200;
    private int totalCount;

    public void setBeanClass(String beanClass) {
        this.beanClass = beanClass;
    }

    public String getBeanClass() {
        return beanClass;
    }

    public List getCol(int col) {
        ArrayList colList = new ArrayList();

        for (int i = 0; i < this.getSize(); i++) {
            colList.add(getValue(col, i));
        }

        return colList;
    }

    public void setCountStatement(String countStatement) {
        this.countStatement = countStatement;
    }

    public String getCountStatement() {
        return countStatement;
    }

    public void setDatabaseUrl(String pDatabaseUrl) {
        this.databaseUrl = pDatabaseUrl;
    }

    public String getDatabaseUrl() {
        return databaseUrl;
    }

    public void setDirty(boolean dirty) {
        this.dirty = dirty;
    }

    public Object getElementAt(int index) {
        return list.get(index);
    }

    public void setFetchSize(int fetchSize) {
        this.fetchSize = fetchSize;
    }

    public int getFetchSize() {
        return fetchSize;
    }

    public void setGroupBy(String groupBy) {
        dirty = true;
        this.groupBy = groupBy;
    }

    public String getGroupBy() {
        return groupBy;
    }

    public void setList(List list) {
        this.list = list;
    }

    public List getList(WebContext pCtx) {
        populate(pCtx);

        return list;
    }

    public List getList() {
        return list;
    }

    public List getMetaData(WebContext pCtx) {
        if ((selectStatement == null) || (selectStatement.length() == 0)) {
            return Collections.EMPTY_LIST;
        }

        if (log.isDebugEnabled()) {
            log.debug("getMetaData");
        }

        String statement = "";

        try {
            statement = (String)com.jb.faces.el.AttributeEvaluator.evaluate(pCtx, this, selectStatement);
        } catch (ELException e) {
            pCtx.addErrorMessage(this, "Failed to parse statement" + selectStatement);

            return Collections.EMPTY_LIST;
        }

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            List list = new ArrayList();
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(statement);

            int i = rs.getMetaData().getColumnCount();
            int j = 1;

            while (j < (i + 1)) {
                list.add(new PropertyInfo(rs.getMetaData().getColumnName(j), rs.getMetaData().getColumnClassName(j)));
                j++;
            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();

            //
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {
                }
            }

            ;

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception e) {
                }
            }

            ;

            DataSourceManager.releaseConnection(this, conn);
        }

        return Collections.EMPTY_LIST;
    }

    public void setOrderBy(String orderBy) {
        dirty = true;
        this.orderBy = orderBy;
    }

    public String getOrderBy() {
        return orderBy;
    }

    public void setPredicate(Predicate predicate) {
        dirty = true;
        this.predicate = predicate;
    }

    public void setSelectStatement(String pSelectStatement) {
        this.dirty = true;
        this.selectStatement = pSelectStatement;
    }

    public String getSelectStatement() {
        return selectStatement;
    }

    public int getSize() {
        if (list == null) {
            return 0;
        } else {
            return list.size();
        }
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }

    public int getTotalCount() {
        return totalCount;
    }

    public Object getValue(int col, int row) {
        return ((List) getElementAt(row)).get(col);
    }

    public void setWhereClause(String whereClause) {
        dirty = true;
        this.whereClause = whereClause;
    }

    public String getWhereClause() {
        return whereClause;
    }

    public void addListDataListener(ListDataListener l) {
    }

    public void remove(WebContext pCtx, Object obj) {
        //readonly for now
    }

    public void removeListDataListener(ListDataListener l) {
    }

    protected void evaluate(FacesContext ctx) {
        super.evaluate(ctx);
        populate((WebContext) ctx);
    }

    private Connection getConnection() throws SQLException {
        return DataSourceManager.getConnection(this);
    }

    private void getCount(WebContext pCtx, Connection conn) {
        totalCount = -1;

        String statement = this.getCountStatement();

        if ((statement == null) || (statement.length() == 0)) {
            return;
        }

        try {
            statement = (String)com.jb.faces.el.AttributeEvaluator.evaluate(pCtx, this, statement);
        } catch (ELException e) {
            pCtx.addErrorMessage(this, "Failed to parse statement" + selectStatement);

            return;
        }

        try {
            statement.trim();

            SelectDBCommand sdbc = new SelectDBCommand(statement, predicate) {
                    public Object populate(Object visitor)
                        throws SQLException {
                        return null;
                    }
                };

            try {
                sdbc.execute(conn);

                while (sdbc.next()) {
                    totalCount = sdbc.getResultSet().getInt(1);
                }
            } finally {
                sdbc.destroy();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void populate(WebContext pCtx) {
        // long l = System.currentTimeMillis();
        if (log.isDebugEnabled()) {
            log.debug("populate " + selectStatement);
        }

        String statement = null;

        try {
            if ((selectStatement == null) || (selectStatement.length() == 0)) {
                return;
            }

            if (log.isDebugEnabled()) {
                log.debug("populate data from database");
            }

            if (!dirty && "session".equals(getScope())) {
                List list = (List) pCtx.getSession().getAttribute(getId());

                if (list != null) {
                    setList(list);

                    return;
                }
            }

            try {
                statement = (String)com.jb.faces.el.AttributeEvaluator.evaluate(pCtx, this, selectStatement);
            } catch (ELException e) {
                pCtx.addErrorMessage(this, "Failed to parse statement" + selectStatement);

                return;
            }

            if (statement == null) {
                pCtx.addErrorMessage(this, "Sql statement is null " + selectStatement);

                return;
            }

            statement.trim();

            if (statement.endsWith(";")) {
                statement = statement.substring(0, statement.length() - 1);
            }

            statement = statement + ((predicate == null) ? "" : (" where " + predicate.toString())) +
                (((groupBy == null) || (groupBy.length() == 0)) ? "" : (" group by " + groupBy)) +
                (((orderBy == null) || (orderBy.length() == 0)) ? "" : (" order by " + orderBy));

            if (fetchSize > 0) {
                statement = statement + " limit " + fetchSize;
            }

            if (!statement.equals(cachedStmt)) {
                cachedStmt = statement;
            }

            Connection conn = null;

            try {
                List list = new ArrayList();

                SelectDBCommand sdbc = new SelectDBCommand(cachedStmt, predicate) {
                        public Object populate(Object list)
                            throws SQLException {
                            ResultSet rs = getResultSet();
                            String beanClass = getBeanClass();

                            if ((beanClass != null) && (beanClass.length() != 0)) {
                                    Object mb = ClassUtil.createInstance(beanClass);  
                                    if(mb==null)
                                        return null;
                                    
                                    com.jb.framework.util.PropUtil.updateWithResultSet(mb, rs);
                                    ((List) list).add(mb);
                                    return mb;
                            } else {
                                List alist = new ArrayList();

                                for (int j = 1; j < (columns + 1); j++)
                                    alist.add(rs.getObject(j));

                                ((List) list).add(alist);

                                return alist;
                            }
                        }
                    };

                try {
                    conn = getConnection();
                    getCount(pCtx, conn);
                    sdbc.execute(conn);
                    columns = sdbc.getResultSet().getMetaData().getColumnCount();

                    int count = 0;
                    int uplimit = 200; //(getCurrentPage() + 2) * getBlockSize();

                    while (sdbc.next()) {
                        sdbc.populate(list);

                        if (count > uplimit) {
                            break;
                        }

                        count++;
                    }
                } finally {
                    sdbc.destroy();
                }

                setList(list);
                dirty = false;

                if ("session".equals(getScope())) {
                    pCtx.getSession().addAttribute(getId(), list);
                }
            } catch (Exception e) {
                if (log.isErrorEnabled()) {
                    log.error("Error statement " + statement, e);
                }

                pCtx.addErrorMessage(this, e.getMessage());
            } finally {
                DataSourceManager.releaseConnection(this, conn);
            }
        } finally {
        }
    }
}
